﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.XPath;
using QuanLyNhanVien.BusinessLogicLayer;

namespace QuanLyNhanVien.PresentationLayer
{
    public partial class ImportData : Form
    {
        string _dirXML = "DataXML.xml";
        List<string> listTableFromXML = new List<string>();
        List<List<string>> listColumnFromXML = new List<List<string>>();
        List<ComboBox> listComboBoxTable = new List<ComboBox>();
        List<ComboBox> listComboBoxColumn = new List<ComboBox>();
        List<string> tabledb = new List<string>();
        List<string> listRemoveControlName = new List<string>();
        Dictionary<int, string> dictionaryTable = new Dictionary<int, string>();
        List<Dictionary<string, string>> dictionaryColumn = new List<Dictionary<string, string>>();
        int tableDBidx;
        int tableXMLidx;
        List<string> listColNameDB = new List<string>();
        int success = 0;
        int failure = 0;
      

        public ImportData()
        {
            InitializeComponent();
        }

        private void ImportData_Load(object sender, EventArgs e)
        {
            success = 0;
            failure = 0;
            this.button_OK.Enabled = false;
            this.button_finish.Enabled = false;
            this.button_Save.Enabled = false;
        }

        private void comboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
        }

        private void button_Import_Click(object sender, EventArgs e)
        {
            listTableFromXML.Clear();
            listColumnFromXML.Clear();

            //Lay danh sach Table & Column trong file xml
            bool success = GetTableAndColumeFromXMLFile(_dirXML);

            if (success)
            {
                ProviderCTL dp = new ProviderCTL();
                tabledb = new List<string>();
                tabledb = dp.ListTables();

                comboBox_TableFromDB.DataSource = null;
                comboBox_TableFromDB.DataSource = tabledb;
                comboBox_TableFromXML.DataSource = null;
                comboBox_TableFromXML.DataSource = listTableFromXML;

                this.button_OK.Enabled = true;
                this.button_finish.Enabled = true;
            }
        }

        /// <summary>
        /// Lay danh sach Table & Column trong file XML
        /// </summary>
        /// <param name="_dirXML"></param>
        /// <returns></returns>
        private bool GetTableAndColumeFromXMLFile(string _dirXML)
        {
            string xmlFileName = _dirXML;
            XPathDocument xmlPathDoc;
            XPathNavigator xNav;
            try
            {
                // create an XPathDocument object
                xmlPathDoc = new XPathDocument(xmlFileName);

                // create a navigator for the xpath doc
                xNav = xmlPathDoc.CreateNavigator();
            }
            catch (Exception ex)
            {
                return false;
            }
            //
            xNav.MoveToRoot();
            xNav.MoveToFirstChild();

            //Bien dem so luong Bang
            int countTable = 0;
            xNav.MoveToFirstChild();
            do
            {
                listTableFromXML.Add(xNav.Name);
                //Move toi cac thuoc tinh cua Row
                xNav.MoveToFirstChild();

                if (xNav.MoveToFirstChild())
                {
                    List<string> cols = new List<string>();
                    do
                    {
                        //Column tuong ung voi tung bang
                        cols.Add(xNav.Name);                       
                    } while (xNav.MoveToNext());
                    listColumnFromXML.Add(cols);

                    xNav.MoveToParent();
                }

                xNav.MoveToParent();
                countTable++;
            } while (xNav.MoveToNext());
            return true;
        }

        private void button_OK_Click(object sender, EventArgs e)
        {
            this.button_Save.Enabled = true;

            //Clean Column Comboboxs
            for (int i = 0; i < listRemoveControlName.Count; i++)
            {
                this.Controls.RemoveByKey(listRemoveControlName[i]);
            }
            

            tableDBidx = comboBox_TableFromDB.SelectedIndex;
            tableXMLidx = comboBox_TableFromXML.SelectedIndex;

            switch ((string)comboBox_TableFromDB.SelectedValue)
            {
                case "NHANVIEN":
                    NhanVienCTL nvCTL = new NhanVienCTL();

                    listColNameDB.Clear();
                    listColNameDB = nvCTL.GetColName();
                    UpdateColNameFromDB(listColNameDB, tableDBidx);
                    break;
                case "PHONGBAN":
                    PhongBanCTL pbCTL = new PhongBanCTL();

                    listColNameDB.Clear();
                    listColNameDB = pbCTL.GetColName();
                    UpdateColNameFromDB(listColNameDB, tableDBidx);
                    break;
                case "NGHIPHEP":
                    NgayNghiCTL nnCTL = new NgayNghiCTL();

                    listColNameDB.Clear();
                    listColNameDB = nnCTL.GetColName();
                    UpdateColNameFromDB(listColNameDB, tableDBidx);
                    break;
            }

        }

        private void UpdateColNameFromDB(List<string> listColNameDB, int tableidx)
        {
            for (int i = 0; i < listColNameDB.Count; i++)
            {
                //Tao Label ten Column trong Database
                Label label = new Label();
                label.AutoSize = true;
                label.Location = new System.Drawing.Point(20, 125 + 25 * i);
                label.Name = "DB" + tableidx.ToString() + listColNameDB[i];
                label.Size = new System.Drawing.Size(32, 13);
                label.TabIndex = 1;
                label.Text = listColNameDB[i];

                listRemoveControlName.Add(label.Name);
                this.Controls.Add(label);

                //Tao ComboBox chon Column tu xml, tuong ung voi moi Label
                ComboBox comboBox = new ComboBox();
                comboBox.FormattingEnabled = true;
                comboBox.Location = new System.Drawing.Point(250, 125 + 25 * i);
                comboBox.Name = "XML" + tableDBidx.ToString() + listColNameDB[i];
                comboBox.Size = new System.Drawing.Size(200, 21);
                comboBox.TabIndex = 0;
                comboBox.SelectedIndexChanged += new System.EventHandler(this.comboBox_SelectedIndexChanged);

                List<string> list = listColumnFromXML[tableXMLidx].ToList();
                comboBox.DataSource = list;

                listRemoveControlName.Add(comboBox.Name);
                this.Controls.Add(comboBox);
                listComboBoxColumn.Add(comboBox);
            }
        }

        private void button_Save_Click(object sender, EventArgs e)
        {
            this.button_Save.Enabled = false;

            //Chua Nhan OK de chon bang
            if (listRemoveControlName.Count == 0)
                return;
            try
            {
                dictionaryTable.Add(tableDBidx, (string)comboBox_TableFromXML.SelectedValue);
            }
            catch (Exception ex)
            {
                return;
            }
            Dictionary<string, string> dictionary = new Dictionary<string,string>();
            for (int i = 0; i < listColNameDB.Count; i++)
            {
                string xmlColName = null;
                for (int j = 0; j < listComboBoxColumn.Count; j++)
                {
                    if (listComboBoxColumn[j].Name == "XML" + tableDBidx.ToString() + listColNameDB[i])
                    {
                        xmlColName = (string)listComboBoxColumn[j].SelectedValue;
                        break;
                    }
                }

                dictionary.Add(listColNameDB[i], xmlColName);
            }
            dictionaryColumn.Add(dictionary);
        }

        private void button_huongDan_Click(object sender, EventArgs e)
        {
            MessageBox.Show("B1: Nhập đường dẫn tới file XML. Rồi nhấn Import \n"
                + "B2: Chọn bảng cần thực hiện. Rồi nhấn OK \n"
                + "B3: Tùy chỉnh tên cột trong xml. Rồi nhấn Save. \n     Thực hiện trở lại B2 đến khi hoàn thành hết các bảng \n"
                + "B4: Chọn Finish");
        }

        /// <summary>
        /// Hoan thanh import data tu file *.xml
        /// Thuc hien doc file xml & Insert du lieu vao Database
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_finish_Click(object sender, EventArgs e)
        {
            string xmlFileName = _dirXML;
            XPathDocument xmlPathDoc;
            XPathNavigator xNav;
            try
            {
                // create an XPathDocument object
                xmlPathDoc = new XPathDocument(xmlFileName);

                // create a navigator for the xpath doc
                xNav = xmlPathDoc.CreateNavigator();
            }
            catch (Exception ex)
            {
                return;
            }
            //
            xNav.MoveToRoot();
            xNav.MoveToFirstChild();

            xNav.MoveToFirstChild();
            do
            {
                if (dictionaryTable.ContainsValue(xNav.Name))
                {
                    List<int> listTableIndex = new List<int>();
                    listTableIndex = dictionaryTable.Where(p => p.Value == xNav.Name).Select(p => p.Key).ToList();

                    for (int k = 0; k < listTableIndex.Count; k++)
                    {
                        for (int i = 0; i < dictionaryTable.Count; i++)
                        {
                            if (dictionaryTable.ElementAt(i).Key == listTableIndex[k])
                            {                             
                                string nameTable = tabledb[dictionaryTable.ElementAt(i).Key];
                                switch (nameTable)
                                {
                                    case "NHANVIEN":
                                        //Move toi cac thuoc tinh cua Row
                                        xNav.MoveToFirstChild();
                                        do
                                        {
                                            if (xNav.MoveToFirstChild())
                                            {
                                                NhanVienCTL nvCTL = new NhanVienCTL();
                                                do
                                                {
                                                    try
                                                    {
                                                        if (xNav.Name == dictionaryColumn[i]["MaNV"])
                                                            nvCTL.NhanVien.MANV = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["HoTenLot"])
                                                            nvCTL.NhanVien.HoTenLot = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["TenNV"])
                                                            nvCTL.NhanVien.TenNV = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["NgaySinh"])
                                                            nvCTL.NhanVien.NgaySinh = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["Phai"])
                                                            nvCTL.NhanVien.Phai = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["DiaChi"])
                                                            nvCTL.NhanVien.DiaChi = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["Luong"])
                                                            nvCTL.NhanVien.Luong = int.Parse(xNav.Value);
                                                        if (xNav.Name == dictionaryColumn[i]["PHG"])
                                                            nvCTL.NhanVien.PHG = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["MA_NQL"])
                                                            nvCTL.NhanVien.MA_NQL = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["SoThanNhan"])
                                                            nvCTL.NhanVien.SoThanNhan = int.Parse(xNav.Value);
                                                        if (xNav.Name == dictionaryColumn[i]["NgayNhanChuc"])
                                                            nvCTL.NhanVien.NgayNhanChuc = xNav.Value;
                                                    }
                                                    catch (Exception ex)
                                                    {
                                                    }

                                                } while (xNav.MoveToNext());

                                                bool kq = nvCTL.insert();
                                                if (kq) success++; else failure++;

                                                xNav.MoveToParent();
                                            }
                                        } while (xNav.MoveToNext());
                                        xNav.MoveToParent();
                                        break;

                                    case "PHONGBAN":
                                        //Move toi cac thuoc tinh cua Row
                                        xNav.MoveToFirstChild();
                                        do
                                        {
                                            if (xNav.MoveToFirstChild())
                                            {
                                                PhongBanCTL pbCTL = new PhongBanCTL();
                                                do
                                                {
                                                    try{
                                                    if (xNav.Name == dictionaryColumn[i]["MAPHG"])
                                                        pbCTL.PhongBan.MAPHG = xNav.Value;
                                                    if (xNav.Name == dictionaryColumn[i]["TENPHG"])
                                                        pbCTL.PhongBan.TenPHG = xNav.Value;
                                                    if (xNav.Name == dictionaryColumn[i]["TRPHG"])
                                                        pbCTL.PhongBan.TRPHG = xNav.Value;
                                                    if (xNav.Name == dictionaryColumn[i]["NG_NhanChuc"])
                                                        pbCTL.PhongBan.NG_NhanChuc = xNav.Value;
                                                    }
                                                    catch (Exception ex)
                                                    {
                                                    }
                                                } while (xNav.MoveToNext());

                                                bool kq = pbCTL.insert();
                                                if (kq) success++; else failure++;

                                                xNav.MoveToParent();
                                            }
                                        } while (xNav.MoveToNext());
                                        xNav.MoveToParent();
                                        break;

                                    case "NGHIPHEP":
                                        //Move toi cac thuoc tinh cua Row
                                        xNav.MoveToFirstChild();
                                        do
                                        {
                                            if (xNav.MoveToFirstChild())
                                            {
                                                NgayNghiCTL nnCTL = new NgayNghiCTL();
                                                do
                                                {
                                                    try
                                                    {
                                                        if (xNav.Name == dictionaryColumn[i]["ID"])
                                                            nnCTL.NgayNghi.ID = int.Parse(xNav.Value);
                                                        if (xNav.Name == dictionaryColumn[i]["MaNV"])
                                                            nnCTL.NgayNghi.MANV = xNav.Value;
                                                        if (xNav.Name == dictionaryColumn[i]["Thang"])
                                                            nnCTL.NgayNghi.Thang = int.Parse(xNav.Value);
                                                        if (xNav.Name == dictionaryColumn[i]["Nam"])
                                                            nnCTL.NgayNghi.Nam = int.Parse(xNav.Value);
                                                        if (xNav.Name == dictionaryColumn[i]["SoNgayNghi"])
                                                            nnCTL.NgayNghi.SoNgayNghi = int.Parse(xNav.Value);
                                                    }
                                                    catch (Exception ex)
                                                    {
                                                    }
                                                } while (xNav.MoveToNext());

                                                bool kq = nnCTL.insert();
                                                if (kq) success++; else failure++;

                                                xNav.MoveToParent();
                                            }
                                        } while (xNav.MoveToNext());
                                        xNav.MoveToParent();
                                        break;
                                }
                            }
                        }
                    }
                        

                }
            } while (xNav.MoveToNext());

            MessageBox.Show("Success: " + success.ToString() + "\nFailure: " + failure.ToString());
            this.Dispose();
        }

        private void button_browse_Click(object sender, EventArgs e)
        {
            DialogResult result = openFileDialog_dirXML.ShowDialog();
            if (result == DialogResult.OK)
            {
                textBox_DirXML.Text = openFileDialog_dirXML.FileName;
                this._dirXML = textBox_DirXML.Text;
            }
        }

        private void textBox_DirXML_TextChanged(object sender, EventArgs e)
        {
            this._dirXML = this.textBox_DirXML.Text;
        }

        


    }
}
